  with base1 as (
select dt,
    arrival_site,
    arrival_site_code,
    waybill_no ,
    max(case when diff_time>0  and send_next_type=2 then 1 else 0 end) as last_time,
    max(case when diff_time>=0   then 1 else 0 end) as youxiao
from (
    select
    to_date(t.arrival_time) as dt,
    arrival_site,
    arrival_site_code,
    waybill_no,
    send_next_type,
    case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
             else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end diff_time
    from   jms_dm.dm_center_stop_time_dt t where  t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
) t
group by    dt,
    arrival_site,
    arrival_site_code,
    waybill_no
),
base2 as (
select
    dt,
    arrival_site,
    arrival_site_code,
    waybill_no,
    max(fache) as fache,
    max(fanjian) as fanjian
from (
select
to_date(t.arrival_time) as dt,
arrival_site,
arrival_site_code,
waybill_no,
case when (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time)))  >0 and send_next_type=2  then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 else 0 end  as fache,
case when (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time)))  >0 and send_next_type<>2  then (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 else 0 end  as fanjian
from   jms_dm.dm_center_stop_time_dt t where t.send_time <> '0000-0000-0000' and t.arrival_time < t.send_time and t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
)
group by     dt,
    arrival_site,
    arrival_site_code,
    waybill_no
)


insert overwrite table jms_dm.dm_center_stop_time_sum_dt partition ( dt )
select dt as date_time,
    t1.agent_name,
    arrival_site as center_name,
    arrival_site_code as center_name_code,
    sum(a) as all_total,--操作总票数
    sum(c) as send_count,--发件票数
    sum(aa1) as send_avg_time,--发件平均时长
    sum(g) as send_tx,--发件36
    sum(h) as sned_fe,--发件48
    sum(d) as send_car_count,--发车票数
    sum(aa) as send_car_avg_time,--发车平均时长
    sum(e) as send_car_tx,--发车36
    sum(f) as send_car_fe,--发车48
    sum(a)-sum(b) as invalid,
    (sum(g)+sum(h)+sum(e)+sum(f) +(sum(a)-sum(b)) )/(sum(a)*1.00) as tx_rate,
    (sum(g)+sum(e) )/(sum(a)*1.00) as fe_rate,
    (sum(h)+sum(f) )/(sum(a)*1.00) as up_fe_rate,
    t1.agent_code,
    dt
from (
select
    dt ,
    arrival_site,
    arrival_site_code,
    count(waybill_no) as a ,
    sum(youxiao ) as b,
    sum(youxiao ) - sum(last_time ) as c,
    sum(last_time) as d,
    0 as e,
    0 as f,
    0 as g,
    0 as h,
    0 as e1,
    0 as f1,
    0 as g1,
    0 as h1,
    0 as aa,
    0 as aa1
from
    base1 t
group by     dt,
    arrival_site,
    arrival_site_code
union all
select
    dt,
    arrival_site,
    arrival_site_code,
    0 as a ,
    0 as b,
    0 as c,
    0 as d,
    sum(case when fache >=36 and fache<48 then 1 else 0 end ) as e,
    sum(case when fache >=48  then 1 else 0 end ) as f,
    sum(case when fanjian >=36 and fanjian<48 then 1 else 0 end ) as g,
    sum(case when fanjian >=48 then 1 else 0 end ) as h,
    0 as e1,
    0 as f1,
    0 as g1,
    0 as h1,
    0 as aa,
    0 as aa1
from
    base2 t
group by     dt,
    arrival_site,
    arrival_site_code
union all
select
    dt,
    arrival_site,
    arrival_site_code,
    0 as a ,
    0 as b,
    0 as c,
    0 as d,
    0 as e,
    0 as f,
    0 as g,
    0 as h,
    sum(case when m1 >=36 and m1<48 then 1 else 0 end ) as e1,
    sum(case when m1 >=48  then 1 else 0 end ) as f1,
    sum(case when m2 >=36 and m2<48 then 1 else 0 end ) as g1,
    sum(case when m2 >=48 then 1 else 0 end ) as h1,
    avg(m1) as aa,
    avg(m2) as aa1
from (
select
    to_date(t.arrival_time) as dt,
    arrival_site,
    arrival_site_code,
    0 as a ,
    0 as b,
    0 as c,
    0 as d,
    0 as e,
    0 as f,
    0 as g,
    0 as h,
    case when (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) >0 and send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 else 0 end  as m1,
    case when (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) >0 and send_next_type<>2  then (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 else 0 end  as m2
from   jms_dm.dm_center_stop_time_dt t where t.send_time <> '0000-0000-0000' and t.arrival_time < t.send_time  and t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
)
group by     dt,
    arrival_site,
    arrival_site_code
) t
left join  jms_dim.dim_network_whole_massage t1 on t.arrival_site_code=t1.code
    group by     dt,
    arrival_site,
    arrival_site_code,t1.agent_name,t1.agent_code

 distribute by pmod(hash(rand()), 3);
